package Dao;

import java.sql.Connection;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Vao.Book;
import util.JDBCUtil;

public class BookDao {
    public ArrayList queryBookById(String bno) throws Exception{
        ArrayList<Book> books = new ArrayList<>();
        Connection conn = new JDBCUtil().getCon();
        String sql="select * from book where id=?";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1, bno);
        ResultSet rs=ps.executeQuery();
        while(rs.next()){
            Book book=new Book();
            book.setBookId(rs.getInt(1));
            book.setBookName(rs.getString(2));
            book.setBookAuthor(rs.getString(3));
            book.setBookHouse(rs.getString(4));
            book.setBookMeny(rs.getDouble(5));
            books.add(book);
        }
        rs.close();
        ps.close();
        conn.close();
        return books;
    }
    public int updateBook(String name,String author,String house,String money,String id) throws SQLException {
        Connection conn = new JDBCUtil().getCon();
        String sql="update book set bookName=?,bookAuthor=?,bookHouse=?,bookMoney=? where bookId=?";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1,name);
        ps.setString(2,author);
        ps.setString(3,house);
        ps.setString(4,money);
        ps.setString(5,id);
        int i=ps.executeUpdate();
        ps.close();
        conn.close();
        return i;
    }
    public int deleteBook(String deId) throws SQLException {
        Connection conn = new JDBCUtil().getCon();
        String sql="delete from book where bookId=? or bookName=?";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1,deId);
        ps.setString(2,deId);
        int i=ps.executeUpdate();
        ps.close();
        conn.close();
        return i;
    }
    public ResultSet getContent(String bookId) throws SQLException {
        Connection conn = new JDBCUtil().getCon();
        String sql="select bookContent from bookcontent where bookId=?";
        PreparedStatement ps =conn.prepareStatement(sql);
        ps.setString(1,bookId);
        ResultSet rs=ps.executeQuery();
        return rs;
    }
    public int insertBook(String name,String author,String house,String money,String content) throws SQLException {
        Connection conn = new JDBCUtil().getCon();
        String sql="INSERT INTO book(bookName,bookAuthor,bookHouse,bookMoney) values(?,?,?,?);";
        PreparedStatement ps =conn.prepareStatement(sql);
//    ps.setString(1,id);
        ps.setString(1,name);
        ps.setString(2,author);
        ps.setString(3,house);
        ps.setString(4,money);
        int i=ps.executeUpdate();
        ps.close();
        conn.close();
        return i;
    }
    public List<Book> findBook(String selectId) throws SQLException {
        Connection conn = new JDBCUtil().getCon();
        String sql="select * from book where bookId=? or bookName=?";
        PreparedStatement ps= conn.prepareStatement(sql);
        ps.setString(1,selectId);
        ps.setString(2,selectId);
        ResultSet rs=ps.executeQuery();
        List<Book> books = new ArrayList<>();
        while(rs.next()){
            String id=rs.getString("bookId");
            String name=rs.getString("bookName");
            String author=rs.getString("bookAuthor");
            String house=rs.getString("bookHouse");
            String money=rs.getString("bookMoney");
            Book book = new Book();
            book.setBookId(Integer.parseInt(id));
            book.setBookName(name);
            book.setBookAuthor(author);
            book.setBookHouse(house);
            book.setBookMeny(Double.parseDouble(money));
            books.add(book);
        }
        rs.close();
        ps.close();
        conn.close();
        return books;
    }
    public List<Book> findAllBook() throws SQLException {
        Connection conn = new JDBCUtil().getCon();
        String sql="select * from book";
        PreparedStatement ps= conn.prepareStatement(sql);
        ResultSet rs=ps.executeQuery();
        List<Book> books = new ArrayList<>();
        while(rs.next()){
            String id=rs.getString("bookId");
            String name=rs.getString("bookName");
            String author=rs.getString("bookAuthor");
            String house=rs.getString("bookHouse");
            String money=rs.getString("bookMoney");
            Book book = new Book();
            book.setBookId(Integer.parseInt(id));
            book.setBookName(name);
            book.setBookAuthor(author);
            book.setBookHouse(house);
            book.setBookMeny(Double.parseDouble(money));
            books.add(book);
        }
        rs.close();
        ps.close();
        conn.close();
        return books;
    }
}
